﻿Imports System.Data.OleDb
Public Class frmInThongKeXuatKhoNoiBo
    'ReportType 1- ngày; 2- tháng; 3-năm; 4-từ ngày đến ngày
    Public ReportType As Integer
    Public Ngay As String
    Public Thang As String
    Public Nam As String

    Public TuNgay As String
    Public DenNgay As String
    Public sTuNgay As String
    Public sDenNgay As String
    Public dtNhapKho As DataTable
    Public SoPhieu As String

    Private Sub frmInThongKeXuatKho_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.WindowState = FormWindowState.Maximized
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
            Case 4
                Report_TheoSoPhieu()
        End Select
    End Sub
    Protected Function Create_NhapKho() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim So As DataColumn = New DataColumn("So")
            So.DataType = System.Type.GetType("System.String")
            So.DefaultValue = ""
            dtTable.Columns.Add(So)

            Dim Ngay As DataColumn = New DataColumn("Ngay")
            Ngay.DataType = System.Type.GetType("System.String")
            Ngay.DefaultValue = ""
            dtTable.Columns.Add(Ngay)

            Dim MaSo As DataColumn = New DataColumn("MaSo")
            MaSo.DataType = System.Type.GetType("System.String")
            MaSo.DefaultValue = ""
            dtTable.Columns.Add(MaSo)

            Dim TenGoi As DataColumn = New DataColumn("TenGoi")
            TenGoi.DataType = System.Type.GetType("System.String")
            TenGoi.DefaultValue = ""
            dtTable.Columns.Add(TenGoi)

            Dim SoLuong As DataColumn = New DataColumn("SoLuong")
            SoLuong.DataType = System.Type.GetType("System.String")
            SoLuong.DefaultValue = ""
            dtTable.Columns.Add(SoLuong)

            Dim DienGiai As DataColumn = New DataColumn("DienGiai")
            DienGiai.DataType = System.Type.GetType("System.String")
            DienGiai.DefaultValue = ""
            dtTable.Columns.Add(DienGiai)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
    Private TongNhap As Single = 0
    Public Sub Report_TheoSoPhieu()
        Try
            Dim objReport As New rptChiTietXuatKhoNoiBo
            objReport.Database.Tables("dtXuatKho").SetDataSource(dtNhapKho)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Số phiếu " & SoPhieu
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoNgay()
        Try
            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where Xoa=0 And TrangThai=N'Đã duyệt' And NgayNhapPhieu='" & Ngay & "'"
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,SP.MaSanPham,SP.TenSanPham,CT.SoLuongThuc,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NgayNhapPhieu='" & Ngay & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham")
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        detailRow = dtNhapKho.NewRow
            '        detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
            '        detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
            '        detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
            '        detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
            '        SoLuong = 0
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
            '            SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongThuc"))
            '        Next
            '        TongNhap = TongNhap + SoLuong
            '        detailRow.Item("SoLuong") = FormatNumber(SoLuong, False)
            '        detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
            '        dtNhapKho.Rows.Add(detailRow)
            '    End If
            'Next
            'dbConn.Close()


            Dim objReport As New rptChiTietXuatKhoNoiBo
            objReport.Database.Tables("dtXuatKho").SetDataSource(dtNhapKho)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            formatDate2VN(Ngay)
            newRow("Title") = "Ngày " & Ngay
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoThang()
        Try
            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where Xoa=0 And TrangThai=N'Đã duyệt' And ThangNhap='" & Thang & "'"
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,SP.MaSanPham,SP.TenSanPham,CT.SoLuongThuc,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.ThangNhap='" & Thang & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham")
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        detailRow = dtNhapKho.NewRow
            '        detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
            '        detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
            '        detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
            '        detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
            '        SoLuong = 0
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
            '            SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongThuc"))
            '        Next
            '        TongNhap = TongNhap + SoLuong
            '        detailRow.Item("SoLuong") = FormatNumber(SoLuong, False)
            '        detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
            '        dtNhapKho.Rows.Add(detailRow)
            '    End If
            'Next
            'dbConn.Close()
            Dim objReport As New rptChiTietXuatKhoNoiBo
            objReport.Database.Tables("dtXuatKho").SetDataSource(dtNhapKho)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Tháng " & Thang
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoNam()
        Try
            TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String

            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where Xoa=0 And TrangThai=N'Đã duyệt' And NamNhap='" & Nam & "'"
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,SP.MaSanPham,SP.TenSanPham,CT.SoLuongThuc,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NamNhap='" & Nam & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham")
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        detailRow = dtNhapKho.NewRow
            '        detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
            '        detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
            '        detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
            '        detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
            '        SoLuong = 0
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
            '            SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongThuc"))
            '        Next
            '        TongNhap = TongNhap + SoLuong
            '        detailRow.Item("SoLuong") = FormatNumber(SoLuong, False)
            '        detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
            '        dtNhapKho.Rows.Add(detailRow)
            '    End If
            'Next
            'dbConn.Close()
            Dim objReport As New rptChiTietXuatKhoNoiBo
            objReport.Database.Tables("dtXuatKho").SetDataSource(dtNhapKho)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Năm " & Nam
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TuNgayDenNgay()
        Try

            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where Xoa=0 And TrangThai=N'Đã duyệt' And NgayNhapTheoSo <=" & DenNgay & " And NgayNhapTheoSo >=" & TuNgay & ""
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,SP.MaSanPham,SP.TenSanPham,CT.SoLuongThuc,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NgayNhapTheoSo <=" & DenNgay & " And CT.NgayNhapTheoSo >=" & TuNgay & " And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham")
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        detailRow = dtNhapKho.NewRow
            '        detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
            '        detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
            '        detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
            '        detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
            '        SoLuong = 0
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
            '            SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongThuc"))
            '        Next
            '        TongNhap = TongNhap + SoLuong
            '        detailRow.Item("SoLuong") = FormatNumber(SoLuong, False)
            '        detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
            '        dtNhapKho.Rows.Add(detailRow)
            '    End If
            'Next
            'dbConn.Close()
            Dim objReport As New rptChiTietXuatKhoNoiBo
            objReport.Database.Tables("dtXuatKho").SetDataSource(dtNhapKho)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Từ ngày " & sTuNgay & " Đến ngày " & sDenNgay
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
End Class